package com.baichang.taoshunda.dao.impl;

import com.baichang.framework.model.Page;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.baichang.framework.dao.impl.BaseDaoImpl;
import com.baichang.taoshunda.dao.DeliveryClerkDao;
import com.baichang.taoshunda.entity.DeliveryClerk;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class DeliveryClerkDaoImpl extends BaseDaoImpl<DeliveryClerk> implements DeliveryClerkDao {
    /**
     * 查询配送人员列表
     *
     * @param name
     * @param lastTime
     * @param created
     * @param cityId
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page queryDispatcherList(String name, String lastTime, String created, Integer cityId, int nowPage, int pageSize) {
        List list = new ArrayList();
        String sql = "SELECT dc.*,tc.name AS cityName FROM t_delivery_clerk dc INNER JOIN t_city tc ON dc.city_id = tc.id WHERE dc.dis_state IN (1,2) AND tc.status IN (1) AND is_certificate=1";
        if (StringUtils.isNotBlank(name)) {
            sql += " AND dc.name LIKE ?";
            list.add("%" + name + "%");
        }
        if (StringUtils.isNotBlank(lastTime)) {
            sql += " AND dc.last_time LIKE ?";
            list.add("%" + lastTime + "%");
        }
        if (StringUtils.isNotBlank(created)) {
            sql += " AND dc.created LIKE ?";
            list.add("%" + created + "%");
        }
        if (cityId > 0) {
            sql += " AND dc.city_id LIKE ?";
            list.add(cityId);
        }
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 查询配送员订单
     *
     * @param id
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page<Map<String, Object>> dispatcherOrder(Integer id, int nowPage, int pageSize) {
        String sql = "SELECT tr.*,tu.nick_name AS nickName,tb.buss_name AS bussName FROM t_delivery_clerk dc INNER JOIN t_order tr ON tr.dis_id=dc.id INNER JOIN t_user tu ON tu.id=tr.buyer_id \n" +
                " INNER JOIN t_bussiness tb ON tb.id=tr.seller_id  WHERE dis_id=?";
        sql += " order by tr.created desc";
        return pageForMapList(sql, nowPage, pageSize, id);
    }

    /**
     * 查询配送员明细
     *
     * @param id
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page<Map<String, Object>> dispatcherOrderDetails(Integer id, int nowPage, int pageSize) {
        String sql = "SELECT * FROM t_delivery_balance WHERE delivery_id=?";
        return pageForMapList(sql, nowPage, pageSize, id);
    }

    /**
     * 修改配送员信息
     *
     * @param map
     * @param id
     * @return
     */
    @Override
    public int update(Map map, Integer id) {
        String sql = "id=?";
        return update(sql, map, id);
    }

    /**
     * 配送员启用、禁用
     *
     * @param id
     * @param state
     * @return
     */
    @Override
    public int updateState(Integer id, Integer state) {
        String sql = "UPDATE t_delivery_clerk SET dis_state=? WHERE id=?";
        return update(sql, state, id);
    }

    /**
     * 重置配送员密码
     *
     * @param id
     * @param md5
     * @return
     */
    @Override
    public int resetPassword(Integer id, String md5) {
        String sql = "UPDATE t_delivery_clerk SET pwd=? WHERE id=?";
        return update(sql, md5, id);
    }

    /**
     * 根据手机号查询配送人员
     *
     * @param mobile 手机号
     * @return
     */
    @Override
    public DeliveryClerk getDeliveryByPhone(String mobile) {
        String sql = " SELECT * FROM {table} WHERE account=? AND dis_state=1 ORDER BY dis_state DESC,created DESC LIMIT 1";
        return queryForBean(sql, mobile);
    }

    /**
     * 忘记密码修改密码
     *
     * @param account 账号
     * @param newPwd  新密码
     * @return
     */
    @Override
    public int updateForgetPwd(String account, String newPwd) {
        String sql = " UPDATE {table} SET pwd=? WHERE account=? ";
        return update(sql, newPwd, account);
    }

    /**
     * 修改密码
     *
     * @param deliveryClerkId 配送员编号
     * @param pwd             密码
     * @return
     */
    @Override
    public int updatePwd(Integer deliveryClerkId, String pwd) {
        String sql = " UPDATE {table} SET pwd=? WHERE id=? ";
        return update(sql, pwd, deliveryClerkId);
    }

    /**
     * 根据配送员编号获取配送员信息
     *
     * @param deliveryClerkId 配送员编号
     * @return
     */
    @Override
    public Map<String, Object> getDeliveryInfoById(Integer deliveryClerkId) {
        String sql = "SELECT d.account, d.`name`, d.is_certificate, d.is_certification, d.head_pic ,d.is_open, " +
                "( SELECT COUNT(o.id) FROM t_order o WHERE o.dis_id = d.id AND date_format(o.end_time, '%Y-%m') = date_format(now()," +
                " '%Y-%m') ) AS monthCount, ( SELECT COUNT(o.id) FROM t_order o WHERE o.dis_id = d.id AND TO_DAYS(o.end_time) = TO_DAYS(NOW()) )" +
                " AS todayCount FROM t_delivery_clerk d WHERE d.id = ?";
        return queryForMap(sql, deliveryClerkId);
    }

    /**
     * 修改配送员信息
     * 实名认证的名字不能修改
     *
     * @param deliveryClerkId 配送员编号
     * @param headPic         头像
     * @return
     */
    @Override
    public boolean updateInfoById(Integer deliveryClerkId, String headPic, String name) {
        String sql = "UPDATE `t_delivery_clerk` SET `head_pic`=? WHERE id= ?";
        return update(sql, headPic, deliveryClerkId) > 0;
    }

    /**
     * 变更手机号
     * 1.旧手机号获取验证码
     * 2.旧手机号验证验证码
     * 3.新手机号获取验证码
     * 4.新手机号验证验证码
     * 5.修改手机号
     *
     * @param deliveryClerkId 配送员编号
     * @param phone           新手机号
     * @return
     */
    @Override
    public boolean changePhone(Integer deliveryClerkId, String phone) {
        String sql = "UPDATE `t_delivery_clerk` SET `account`=?  WHERE (`id`=?)";
        return update(sql, phone, deliveryClerkId) > 0;
    }


    @Override
    public int realNameAuth(String name, Integer deliveryClerkId, String identityCardNum, String fontCard, String reverseCrad, String handheldPhotos, int isCertification) {
        String sql = " UPDATE {table} SET name=?,identity_card_num=?,font_card=?,reverse_crad=?,handheld_photos=?,is_certification=?,is_certificate=2 WHERE id=? ";
        return update(sql, name, identityCardNum, fontCard, reverseCrad, handheldPhotos, isCertification, deliveryClerkId);
    }

    /**
     * 根据编号获取账号
     *
     * @param deliveryIds
     * @return
     */
    @Override
    public List<Map<String, Object>> getDeliveryClerkAccountsByIds(List<Integer> deliveryIds) {
        String sql = " SELECT * FROM {table} ";
        if (deliveryIds != null && deliveryIds.size() > 0) {
            sql += " WHERE id IN ( ";
            for (Integer id : deliveryIds) {
                sql += id + ",";
            }
            sql = sql.substring(0, sql.length() - 1);
            sql += " ) ";
        }
        sql += " and is_open=1 and dis_state=1";
        return queryForMapList(sql);
    }

    /**
     * 查询认证
     * <p>
     * 查询认证
     *
     * @param startCreate
     * @param endCreate
     * @param
     * @param cityId
     * @param nowPage
     * @param pageSize    @return
     */
    @Override
    public Page queryAuthentication(String startCreate, String endCreate, String startTime, String endTime, String name, Integer cityId, int nowPage, int pageSize) {
        List list = new ArrayList();
        String sql = " SELECT dc.*,tc.name AS cityName FROM t_delivery_clerk dc INNER JOIN t_city tc ON dc.city_id = tc.id WHERE dc.dis_state IN (1,2) AND tc.status IN (1) AND dc.is_certificate=1 AND dc.is_certification=1";
        if (StringUtils.isNotBlank(name)) {
            sql += " AND dc.name LIKE ?";
            list.add("%" + name + "%");
        }
        if (!startTime.equals("") && !endTime.equals("")) {
            sql += " AND dc.last_time>=? AND dc.last_time<=DATE_ADD(?, Interval 1 DAY)";
            list.add(startTime);
            list.add(endTime);
        }
        if (!startCreate.equals("") && !endCreate.equals("")) {
            sql += " AND dc.created>=? AND dc.created<=DATE_ADD(?, Interval 1 DAY)";
            list.add(startCreate);
            list.add(endCreate);
        }
        if (cityId > 0) {
            sql += " AND dc.city_id LIKE ?";
            list.add(cityId);
        }
        sql += " ORDER BY dc.created DESC ";
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 查询未认证
     *
     * @param name
     * @param
     * @param
     * @param cityId
     * @param nowPage
     * @param pageSize @return
     */
    @Override
    public Page queryNoAuthentication(String name, String startCreate, String endCreate, String startTime, String endTime, Integer cityId, int nowPage, int pageSize) {
        List list = new ArrayList();
        String sql = " SELECT dc.*,tc.name AS cityName FROM t_delivery_clerk dc INNER JOIN t_city tc ON dc.city_id = tc.id WHERE dc.dis_state IN (1) AND tc.status IN (1) AND dc.is_certificate=2 AND is_certification=1";
        if (StringUtils.isNotBlank(name)) {
            sql += " AND dc.name LIKE ?";
            list.add("%" + name + "%");
        }
        if (!startTime.equals("") && !endTime.equals("")) {
            sql += " AND dc.last_time>=? AND dc.last_time<=DATE_ADD(?, Interval 1 DAY)";
            list.add(startTime);
            list.add(endTime);
        }
        if (!startCreate.equals("") && !endCreate.equals("")) {
            sql += " AND dc.created>=? AND dc.created<=DATE_ADD(?, Interval 1 DAY)";
            list.add(startCreate);
            list.add(endCreate);
        }
        if (cityId > 0) {
            sql += " AND dc.city_id LIKE ?";
            list.add(cityId);
        }
        sql += " ORDER BY dc.created DESC ";
        return pageForMapList(sql, nowPage, pageSize, list.toArray());
    }

    /**
     * 修改最后登录时间
     *
     * @param id 配送员编号
     * @return
     */
    @Override
    public int updateLastLoginDate(int id) {
        String sql = " UPDATE {table} SET last_time=NOW() WHERE id=? ";
        return update(sql, id);
    }

    /**
     * 修改是否接单状态
     *
     * @param deliveryId 配送员编号
     * @param isOpen     是否接单
     *                   1：接单
     *                   2：停止接单
     * @return
     */
    @Override
    public int updateIsOpen(Integer deliveryId, Integer isOpen) {
        String sql = " UPDATE {table} SET is_open=? WHERE id=? ";
        return update(sql, isOpen, deliveryId);
    }

    @Override
    public boolean updateDeliverBlance(int disId, BigDecimal add) {
        String sql = "UPDATE {table} SET balance=? WHERE id=? ";
        return update(sql, add, disId) > 0;
    }

    /**
     * 配送员通过认证
     *
     * @param id
     * @param isCertificate
     * @return
     */
    @Override
    public int throughDispatcher(Integer id, Integer isCertificate) {
        String sql = " UPDATE {table} SET is_certificate=? WHERE id=? ";
        return update(sql, isCertificate, id);
    }

    /**
     * @param deliveryId
     * @param deliveryMoney
     * @return
     */
    @Override
    public int updateBalance(Integer deliveryId, Double deliveryMoney) {
        String sql = " UPDATE {table} SET balance=? WHERE id=? ";
        return update(sql, deliveryMoney, deliveryId);
    }

    /**
     * @return
     */
    @Override
    public Map<String, Object> deliveryCount() {
        String sql = "SELECT COUNT(*) AS deliveryCount FROM t_delivery_clerk WHERE dis_state=1 AND is_certificate=1 AND is_certification=1";
        return queryForMap(sql);
    }

    @Override
    public List<Map<String, Object>> getKetubbahList(Integer deliveryId, Integer nowPage, Integer pageSize) {
        String sql = "SELECT db.id, db.delivery_id AS deliveryId, db.type, db.consumption_time AS consumptionTime, db.money , db.bail, db.order_number AS orderNumber, db.reason FROM t_delivery_bail db WHERE db.delivery_id= ?";
        return listForMapList(sql, nowPage, pageSize, deliveryId);
    }

    @Override
    public List<Map<String, Object>> findGuide() {
        String sql = " SELECT * FROM t_guide WHERE type=3  ";
        return queryForMapList(sql);
    }
}